type FromToType = {
  from: number | string,
  to: number | string
}
export type InitDbStringType = { name: string, aliasName ?: string }
export type InitSelectWhereType = { key: string, sqlVal: string | number, isDatabase?: boolean }
export type InitSelectLikeType = InitSelectWhereType

export type renderSelectParam = {
  database: InitDbStringType[],
  returnFeild:( { name: string, aliasName?: string, dbName?: string } | string)[],
  where?: InitSelectWhereType[],
  like?: InitSelectLikeType[],
  otherSql?: string
} 
export type renderInsertParam = {
  database: string,
  keyAndVal: { key: string, value: string | number | Date }[]
}

export type renderDelParam = {
  database: string,
  keyAndVal: { key: string, sqlVal: string | number, isDatabase?: boolean }[]
}

export type renderUpdateParam = {
  database: string,
  keyAndVal: { key: string, value: string | number | Date}[],
  where:{ key: string, sqlVal: string | number }[]
}

/**
 * @vuese 将数字或者字符转换为符合 sql 格式
 * @param target 
 * @returns 
 */
export const renderStrOrNum = (target: number | string | Date) => {
  return typeof target === "string" ? `"${target}"` : target 
}

/**
 * @vuese 目标字段的 case 语句
 * @param target 
 * @param fromToArr 
 * @param asTarget 
 * @returns 
 */
export const renderCase = (target: string, fromToArr: FromToType[], asTarget?: string) => {
  let _str = ""
  fromToArr.forEach(item => {
    _str += `WHEN ${target} = ${renderStrOrNum(item.from)} THEN ${renderStrOrNum(item.to)} `
  })
  
  return _str ? `(CASE ${_str}ELSE "未定义" END) AS ${asTarget ? asTarget : target},` : ``
}

/**
 * @vuese 应该有的where 
 */
export const renderShoulWhere = <T extends object>(obj: T, initArr: Array<{ key: keyof T, sqlKey: string }>): string => {
  let _sql = ""
  initArr.forEach(item => {
    if (obj[item.key]) {
      _sql += `${item.sqlKey}=${renderStrOrNum(obj[item.key] as number | string)} and `
    }
  }) 
  return _sql
}

/**
 * @vuese 找 database 的别名
 * @param name 
 * @param database 
 */
export const findDbAliasName = (dbName: string, database: InitDbStringType[]): string => {
  const dbModel = database.find(db => db.name === dbName)
  return dbModel?.aliasName ? dbModel.aliasName : dbModel.name
}

export const delLastStr = (str: string, num = 1) => str.substr(0, str.length - num)

/**
 * @vuese 创建 select sql 字符串
 * @returns string
 */
export const renderSelectSql = (config: renderSelectParam): string => {
  const { database, returnFeild, where ,like} = config
  
  const feildString = () => {
    let _sReturn = ""
    
    returnFeild.forEach((model) => {
      let str = ""
      if (typeof model === "string") {
        str = `${model},`
      } else {
        const { dbName, name, aliasName } = model
        const db = database && dbName ? `${findDbAliasName(dbName, database)}.` : ""
        str = `${db}${name} ${aliasName ? aliasName: name},`
      }
      
      _sReturn += str
    })

    return delLastStr(_sReturn)
  }

  const dbString = () => {
    let _sDb = ""
    database.forEach(db => {
      _sDb += `${db.name} ${db.aliasName ? db.aliasName :""},`
    })

    return delLastStr(_sDb)
  }

  const whereString = () => {
    let _sWhere = "WHERE "
    where?.length && where.forEach(w => {
      _sWhere += `${w.key} = ${w.isDatabase ? w.sqlVal :renderStrOrNum(w.sqlVal)} AND `
    })

    like?.length && like.forEach(l => {
      _sWhere += `${l.key} LIKE "%${l.sqlVal}%" AND `
    })

    return delLastStr(_sWhere,4)
  }

  return `SELECT ${feildString()} FROM ${dbString()} ${config.where?.length || config.like?.length ? whereString(): ``} ${config.otherSql || ''}`
};

/**
 * @use 创建 insert sql 查询字符串
 * @param config 
 * @returns 
 */
export const renderInsertSql = (config: renderInsertParam): string => {
  //INSERT INTO users(id, name, age) VALUES(123, '姚明', 25);
  let keyStr = ``, valueStr = ``
  config.keyAndVal.forEach((v) => {
    keyStr += `${v.key},`
    valueStr += v.value instanceof Date ? `Now(),` : `${renderStrOrNum(v.value)},`
  })
  return `INSERT INTO ${config.database}(${delLastStr(keyStr,1)}) VALUES(${delLastStr(valueStr,1)})`
}

/**
 * @use 创建 delete sql
 * @param config 
 */
export const renderDelSql = (config: renderDelParam) => {
  console.log(config);
  
  const whereStr = () => {
    let _sWhere = 'Where '
    config.keyAndVal.forEach(w => {
      _sWhere += `${w.key} = ${w.isDatabase ? w.sqlVal :renderStrOrNum(w.sqlVal)} AND `
    })

    return delLastStr(_sWhere, 4)
  }

  return `DELETE FROM ${config.database} ${whereStr()}`
}

/**
 * @use 创建 update sql
 */
export const renderUpdateSql = (config: renderUpdateParam) => {
  const setStr = () => {
    let _str = `SET `;
    config.keyAndVal.forEach(w => {
      _str +=  w.value instanceof Date ? `${w.key} = NOW(),` : `${w.key} = ${renderStrOrNum(w.value)},`
    })

    return delLastStr(_str, 1)
  }

  const whereStr = () => {
    let _sWhere = 'Where '
    config.where.forEach(w => {
      _sWhere +=`${w.key} = ${renderStrOrNum(w.sqlVal)} AND `
    })

    return delLastStr(_sWhere, 4)
  }
  
  return `UPDATE ${config.database} ${setStr()} ${whereStr()}`
}